﻿/*
* 创建表
*/
if exists (select * from sysobjects where id = object_id(N'T_Sale') and OBJECTPROPERTY(id, N'IsTable') = 1)
	drop table [dbo].[T_Sale]
GO
CREATE TABLE [dbo].[T_Sale]
(
	[SaleId] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, 
    [SaleCode] VARCHAR(50) NULL, 
    [SaleTime] VARCHAR(20) NULL, 
    [CustomerId] UNIQUEIDENTIFIER NULL, 
    [Salesman] VARCHAR(50) NULL, 
    [ExchangeRate] DECIMAL(18, 6) NULL, 
    [Freight] DECIMAL(18, 6) NULL, 
    [PayFreight] DECIMAL(18, 6) NULL, 
    [Payment] VARCHAR(200) NULL, 
    [StateId] INT NULL, 
    [Description] VARCHAR(5000) NULL, 
    [CreateUserId] INT NULL, 
    [CreateTime] VARCHAR(20) NULL, 
    [UpdateUserId] INT NULL, 
    [UpdateTime] VARCHAR(20) NULL, 
    [Valid] INT NULL
)
GO

if exists (select * from sysobjects where id = object_id(N'T_SaleDetail') and OBJECTPROPERTY(id, N'IsTable') = 1)
	drop table [dbo].[T_SaleDetail]
GO
CREATE TABLE [dbo].[T_SaleDetail]
(
    [SaleDetailId] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, 
	[SaleId] UNIQUEIDENTIFIER NOT NULL, 
    [PurchaseTime] VARCHAR(20) NULL, 
    [PurchaseCode] VARCHAR(50) NULL, 
	[PurchaseReceipt] VARCHAR(50) NULL, 
	[PurchasePrice] DECIMAL(18, 6) NULL, 
	[PurchaseAmount] DECIMAL(18, 6) NULL, 
    [Quantity] DECIMAL(18, 6) NULL,
    [SupplierId] UNIQUEIDENTIFIER NULL, 
	SupplierName varchar(100) NULL,
	[SalePriceUSD] DECIMAL(18, 6),
	[SalePriceRMB] DECIMAL(18, 6) NULL,
    [SaleAmountUSD] DECIMAL(18, 6) NULL, 
    [SaleAmountRMB] DECIMAL(18, 6) NULL, 
    [CreateUserId] INT NULL, 
    [CreateTime] VARCHAR(20) NULL, 
    [UpdateUserId] INT NULL, 
    [UpdateTime] VARCHAR(20) NULL, 
    [Valid] INT NULL
)


/*
* 修改表结构
*/

if exists(
	select 1 from sys.all_objects A,sys.all_columns B
	where A.object_id=B.object_id and A.name='T_ProgressRecord' and B.name = 'NextRecordTime'
)
 begin
	print '字段【NextRecordTime】已存在'
 end
else
 begin
	alter table T_ProgressRecord add NextRecordTime varchar(20) default ''
 end

if exists(
	select 1 from sys.all_objects A,sys.all_columns B
	where A.object_id=B.object_id and A.name='T_ProgressRecord' and B.name = 'NextRecordContent'
)
 begin
	print '字段【NextRecordContent】已存在'
 end
else
 begin
	alter table T_ProgressRecord add NextRecordContent varchar(1000) default ''
 end


if exists(
	select 1 from sys.all_objects A,sys.all_columns B
	where A.object_id=B.object_id and A.name='T_ProgressRecord' and B.name = 'NextRecordRead'
)
 begin
	print '字段【NextRecordRead】已存在'
 end
else
 begin
	alter table T_ProgressRecord add NextRecordRead int default 0
 end


if exists (select * from sysobjects where id = object_id(N'T_Node') and OBJECTPROPERTY(id, N'IsTable') = 1)
	drop table [dbo].[T_Node]

GO
CREATE TABLE [dbo].[T_Node]
(
	[NodeId] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, 
    [Title] VARCHAR(200) NULL, 
    [Content] VARCHAR(8000) NULL, 
    [RemindTime] VARCHAR(20) NULL, 
    [CreateTime] VARCHAR(20) NULL, 
    [CreateUserId] INT NULL, 
    [UpdateTime] VARCHAR(20) NULL, 
    [UpdateUserId] INT NULL, 
    [Valid] INT NULL
)
GO

/*
* 更新视图
*/
if exists (select * from sysobjects where id = object_id(N'View_T_ProgressRecord') and OBJECTPROPERTY(id, N'IsView') = 1)
	drop view [dbo].[View_T_ProgressRecord]

GO
CREATE VIEW [dbo].[View_T_ProgressRecord]
AS 
SELECT 
T_ProgressRecord.* 
,CustomerName=Sys_User.UserName
,RecordTypeName=RT.ClassificationName
,ProgressTypeName=PT.ClassificationName
FROM T_ProgressRecord
left outer join Sys_User on Sys_User.UserId=T_ProgressRecord.CreateUserId
left outer join T_Classification as RT on RT.ClassificationId=T_ProgressRecord.RecordTypeId
left outer join T_Classification as PT on PT.ClassificationId=T_ProgressRecord.ProgressTypeId

GO


if exists (select * from sysobjects where id = object_id(N'View_T_Customer') and OBJECTPROPERTY(id, N'IsView') = 1)
	drop view [dbo].[View_T_Customer]
GO
CREATE VIEW [dbo].[View_T_Customer]
AS 
SELECT 
T.* 
,CreateUserName=CTUser.UserName
,UpdateUserName=UTUser.UserName
,CustomerTypeName=CustomerType.ClassificationName
,NextRecordTime=(select top 1 NextRecordTime from T_ProgressRecord where T_ProgressRecord.CustomerId=T.CustomerId order by NextRecordTime desc)
,NextRecordContent=(select top 1 NextRecordContent from T_ProgressRecord where T_ProgressRecord.CustomerId=T.CustomerId order by NextRecordTime desc)
FROM T_Customer T
left outer join Sys_User as CTUser on T.CreateUserId=CTUser.UserId
left outer join Sys_User as UTUser on T.CreateUserId=UTUser.UserId
left outer join T_Classification as CustomerType 
	on ParentId=(select top 1 ClassificationId from T_Classification where ClassificationCode='CustomerType')
	and CustomerType.ClassificationId = T.CustomerTypeId

GO


if exists (select * from sysobjects where id = object_id(N'View_T_Sale') and OBJECTPROPERTY(id, N'IsView') = 1)
	drop view [dbo].[View_T_Sale]
GO
CREATE VIEW [dbo].[View_T_Sale]
AS 

select 
T.* 
,StateName=SaleState.ClassificationName
,T_Customer.CustomerName
from T_Sale as T
left outer join T_Customer on T.CustomerId=T_Customer.CustomerId
left outer join T_Classification as SaleState 
	on ParentId=(select top 1 ClassificationId from T_Classification where ClassificationCode='SaleState')
	and SaleState.ClassificationId = T.StateId

GO

























/*
* 添加菜单
*/
if not exists(select 1 from Sys_Module where [ModuleCode]='005')
 begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('销售管理','005',0,1,'',0,'04','','001')
 end
else
 begin
	print '【销售管理】菜单模块已存在'
 end

declare @NewModuleId int;

--销售管理
select @NewModuleId=ModuleId from Sys_Module where [ModuleCode]='005'

if not exists(select 1 from Sys_Module where [ModuleCode]='005001')
 begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('销售单录入','005001Add',@NewModuleId,1,'/Sale/SaleEdit.aspx?Action=HAdd',1,'01','','001')
 end
else
 begin
	print '【销售单录入】菜单模块已存在'
 end

if not exists(select 1 from Sys_Module where [ModuleCode]='005001')
 begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('销售单管理','005001',@NewModuleId,1,'/Sale/SaleList.aspx',1,'02','','001')
 end
else
 begin
	print '【销售单管理】菜单模块已存在'
 end







 /*----------------------------------------------
添加类型
----------------------------------------------*/
if exists(select 1 from T_Classification where ClassificationCode='SaleState')
 begin
    print '【销售单据状态】已存在'
 end
else
 begin
	insert into T_Classification (ParentId,ClassificationCode,ClassificationName)
	values(0,'SaleState','销售单据状态');

	declare @ParentClassificationId int;
	select @ParentClassificationId=ClassificationId from T_Classification where ClassificationCode='SaleState'

	insert into T_Classification (ParentId,ClassificationCode,ClassificationName)
	values(@ParentClassificationId,'','下单');

	insert into T_Classification (ParentId,ClassificationCode,ClassificationName)
	values(@ParentClassificationId,'','已发货');

	insert into T_Classification (ParentId,ClassificationCode,ClassificationName)
	values(@ParentClassificationId,'','已签收');
 end



 /*----------------------------------------------
添加菜单
----------------------------------------------*/
if not exists(select 1 from Sys_Module where [ModuleCode]='005101')
 begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('销售状态管理','005101',@NewModuleId,1,'/Phone/Classification.aspx?Code=SaleState',1,'10','','001')
 end
else
 begin
	print '【销售状态管理】已存在'
 end



select SaleCode from T_Sale
select max(SaleCode) from T_Sale where SaleCode like 'Sale201412%'